CREATE                            PROCEDURE [dbo].[asi_CreatePaymentSchedules]
AS
DECLARE @invKey uniqueidentifier
DECLARE @invLineKey uniqueidentifier
DECLARE @prevInvKey uniqueidentifier
DECLARE @prevInvLineKey uniqueidentifier
DECLARE @paymentTermsKey uniqueidentifier
DECLARE @prevPaymentTermsKey uniqueidentifier
DECLARE @ARAcctKey uniqueidentifier
DECLARE @scheduleLineKey uniqueidentifier
DECLARE @invDate datetime
DECLARE @prevInvDate datetime
DECLARE @termsCode nvarchar(50)
DECLARE @earlyDiscountDays int
DECLARE @earlyDiscountPercent decimal(18,4)
DECLARE @intSize int
DECLARE @prevIntSize int
DECLARE @intType int
DECLARE @prevIntType int
DECLARE @intCount int
DECLARE @prevIntCount int
DECLARE @payments int
DECLARE @fetchStatus int
DECLARE @firstIteration int
DECLARE @acctMethod nchar(1)
DECLARE @prevAcctMethod nchar(1)
DECLARE @extendedPrice decimal(18,4)
DECLARE @currentTotal decimal(18,4)
DECLARE @invoiceTotal decimal(18,4)
DECLARE @currentPayment int
DECLARE @tempARKey uniqueidentifier
DECLARE @acctTotal decimal(18,4)
DECLARE @acctAmount decimal(18,4)
DECLARE @share decimal(18,4)
DECLARE @paidToCurrentGL decimal(18,4)
DECLARE @priority int
DECLARE @paymentDate datetime
DECLARE @firstPaymentDate datetime
DECLARE @scheduledSoFarForInvoice decimal(18,4)
DECLARE @dueAmount decimal(18,4)
DECLARE @scheduledSoFarForInstallment decimal(18,4)
DECLARE @firstPaymentDateTypeCode int
DECLARE @prevFirstPaymentDateTypeCode int
DECLARE @firstPaymentDateOffset int
DECLARE @prevFirstPaymentDateOffset int
DECLARE @firstPaymentDueDate datetime
DECLARE @prevFirstPaymentDueDate datetime
DECLARE @payPriority int
declare @endOfNextMonth datetime

/* enums */
DECLARE @termsTypeYearly int
DECLARE @termsTypeQuarterly int
DECLARE @termsTypeMonthly int
DECLARE @termsTypeWeekly int
DECLARE @termsTypeDaily int
SET @termsTypeYearly = 0
SET @termsTypeQuarterly = 1
SET @termsTypeMonthly = 2
SET @termsTypeWeekly = 3
SET @termsTypeDaily = 4

DECLARE @firstPaymentDateTypeInvoiceDate int
DECLARE @firstPaymentDateTypeNextMonth int
DECLARE @firstPaymentDateTypeEndOfMonth int
DECLARE @firstPaymentDateTypeSpecificDate int
SET @firstPaymentDateTypeInvoiceDate = 0
SET @firstPaymentDateTypeNextMonth = 1
SET @firstPaymentDateTypeEndOfMonth = 2
SET @firstPaymentDateTypeSpecificDate = 3

/*  This stored procedure creates a payment schedule structure
    for each invoice in the #tmpInvoice table created by the caller.
    - Create a cursor on a left join of the tmpInvoice table and the
      payment terms table.  The result has one line for every invoice
      line being processed, ordered by the invoice key.  
    - Read through these lines, accumulating information
      about the current invoice until all  lines for that
      invoice have been processed.  
    - For each payment required
      (based on the payment terms) create a PaymentScheduleLine row.  
      There may be multiple PaymentScheduleLine rows based on the applicable GLAccounts.
    TBD: This procedure assumes that we round everything to two decimal places.
         Perhaps the real number ought to passed in as a parameter.
*/
-- invoiceTotal will hold the total amount for the current invoice
SET @invoiceTotal = 0
-- firstIteration is set to 1 initially; during the first iteration of the
-- enclosing cursor loop, it is set to 0
SET @firstIteration = 1

-- The Totals table variable accumulates information about each invoice in turn.
-- The key for this table designates the GLAccount being used by the distribution
-- lines attached to this invoice.
DECLARE @Totals TABLE
    (GLAccountKey uniqueidentifier PRIMARY KEY,
      Total decimal(18,4),
     Share decimal(18,4),
     Paid decimal(18,4),
     Priority int)

-- Create a cursor on the temp table of all invoice distributions joined with the payment terms
DECLARE Get_Invoices CURSOR FOR
SELECT InvoiceKey, InvoiceLineKey, AccountingMethodCode, PaymentTermsKey = CASE WHEN #tmpInvoice.PaymentTermsKey = '00000000-0000-0000-0000-000000000000' THEN null ELSE #tmpInvoice.PaymentTermsKey END,
InvoiceDate, ExtendedPrice, PaymentTermsName, EarlyDiscountDays, EarlyDiscountPercent, PaymentIntervalSize, PaymentIntervalTypeCode, PaymentIntervalCount,
ARGLAccountKey, FirstPaymentDueDate, FirstPaymentDateTypeCode, FirstPaymentDateOffset, PayPriority
FROM #tmpInvoice
LEFT OUTER JOIN PaymentTerms ON #tmpInvoice.PaymentTermsKey = PaymentTerms.PaymentTermsKey
WHERE #tmpInvoice.InvoiceTypeCode = 'S'
AND ApplyingCredit = 0
AND ARGLAccountKey is not null
ORDER BY InvoiceKey, InvoiceLineKey
SET @prevInvKey = NEWID()
OPEN Get_Invoices
FETCH next from Get_Invoices into
    @invKey, @invLineKey, @acctMethod, @paymentTermsKey, @invDate, @extendedPrice, @termsCode, @earlyDiscountDays, @earlyDiscountPercent, @intSize, @intType, @intCount,
    @ARAcctKey, @firstPaymentDueDate, @firstPaymentDateTypeCode, @firstPaymentDateOffset, @payPriority
WHILE 1 = 1
BEGIN
    SET @fetchStatus = @@FETCH_STATUS
    IF @fetchStatus = -1 AND @firstIteration = 1
        BREAK
    /* we only want to use the date part; lose the time... */
    SET @invDate = convert(char(10), @invDate, 101)
    /*  We are finished with the most recent invoice IF:
        - we are finished reading all the invoice lines
        OR
          - the current invoice key is different from the previous one
          AND
          - the firstIteration flag is not one.
        If we are finished with an invoice, we need to use the associated
        payment terms (if any) to create PaymentScheduleLines.
     */
    IF ((@fetchStatus = -1)) OR ((@prevInvKey != @invKey) AND @firstIteration != 1)
    BEGIN
        
        /* Compute the shares of each of the accounts in this invoice */
        DECLARE FigureShares cursor for
        SELECT GLAccountKey, Total
        FROM @Totals
        OPEN FigureShares
        FETCH next from FigureShares into
            @tempARKey, @acctTotal
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @share = ROUND(@acctTotal / @invoiceTotal, 2)
            UPDATE @Totals SET Share = @share WHERE GLAccountKey = @tempARKey
            FETCH next from FigureShares into
                @tempARKey, @acctTotal
        END
        CLOSE FigureShares
        DEALLOCATE FigureShares
        
        
        /* Next, write the schedule lines */
        /* If there are payment terms for this invoice, use the information in
           the payment terms object to set the number of payments.  Otherwise,
           set it to one.
        */
        IF @prevPaymentTermsKey IS NULL
            SET @payments = 1
        ELSE
            SET @payments = @prevIntCount
        IF @payments = 0
            SET @payments = 1
        SET @currentPayment = 0
        SET @scheduledSoFarForInvoice = 0
        SET @firstPaymentDate = @prevInvDate
        
        /* compute the first payment date */
        IF @prevPaymentTermsKey IS NOT NULL
        BEGIN
            SET @endOfNextMonth = dateadd(dd,-1,DateAdd(mm, DateDiff(mm, 0, @prevInvDate) + 2, 0))
            SET @firstPaymentDate =
                CASE @prevFirstPaymentDateTypeCode
                    --  add the supplied offset (or zero if null) to the invoice date
                    WHEN @firstPaymentDateTypeInvoiceDate THEN
                        DATEADD(dd, ISNULL(@prevFirstPaymentDateOffset, 0), @prevInvDate)
                    --  add the supplied offset (or zero if null) to the beginning of next month.
                    --  if the result would be greater than the end of next month (e.g. the offset was 30
                    --  and next month February) then just return the end of next month.
                    WHEN @firstPaymentDateTypeNextMonth THEN
                        CASE WHEN DATEADD(d, ISNULL(@prevFirstPaymentDateOffset, 0), DateAdd(mm, DateDiff(mm, 0, @prevInvDate) + 1, -1)) > @endOfNextMonth
                             THEN @endOfNextMonth
                             ELSE  DATEADD(d, ISNULL(@prevFirstPaymentDateOffset, 0), DATEADD(m, DATEDIFF(m, 0, @prevInvDate) + 1, -1))
                             END
                    -- return the last day of the current month
                    WHEN @firstPaymentDateTypeEndOfMonth THEN
                        DATEADD(d, -1, DATEADD(m, 1 + DATEDIFF(m, 0, @prevInvDate), 0))
                    -- return the indicated date (or the invoice date, if null)
                    WHEN @firstPaymentDateTypeSpecificDate THEN
                        ISNULL(@prevFirstPaymentDueDate, @firstPaymentDate)
                END
        END
        /* compute the amount */
        SET @dueAmount = ROUND(@invoiceTotal / @payments, 2)
        WHILE @currentPayment < @payments
        BEGIN
            SET @scheduledSoFarForInstallment = 0
            /* Compute the due date
               If there are no payment terms, we will just leave the
                                     the payment date as the invoice date.*/
            IF @prevPaymentTermsKey IS NOT NULL
            BEGIN
                SET @paymentDate =
                    CASE @prevIntType
                        WHEN @termsTypeYearly THEN DATEADD(yy, @prevIntSize * @currentPayment, @firstPaymentDate)
                        WHEN @termsTypeQuarterly THEN DATEADD(qq, @prevIntSize * @currentPayment, @firstPaymentDate)
                        WHEN @termsTypeMonthly THEN DATEADD(mm, @prevIntSize * @currentPayment, @firstPaymentDate)
                        WHEN @termsTypeWeekly THEN DATEADD(wk, @prevIntSize * @currentPayment, @firstPaymentDate)
                        WHEN @termsTypeDaily THEN DATEADD(dd, @prevIntSize * @currentPayment, @firstPaymentDate)
                        /* TBD: what to do otherwise? */
                    END
            END
            ELSE
                SET @paymentDate = @firstPaymentDate
            SET @currentPayment = @currentPayment + 1
            SET @scheduledSoFarForInvoice = @scheduledSoFarForInvoice + @dueAmount
            /*
                If we are working on the last payment for this invoice,
                we want to correct for round-off error.  If scheduledSoFar
                is not the same as the invoiceTotal, adjust this last
                payment.
            */
            IF (@currentPayment = @payments) AND (@scheduledSoFarForInvoice != @invoiceTotal)
                SET @dueAmount = @dueAmount + (@invoiceTotal - @scheduledSoFarForInvoice)
            
            
            IF UPPER(@prevAcctMethod) = 'A'
            BEGIN
                /* For each AR in the invoice, create schedule lines to divide the
                   installment amount according to the share for this AR */
                DECLARE Account_Info cursor for
                SELECT GLAccountKey, Total, Share, Paid, Priority
                FROM @Totals
                OPEN Account_Info
                FETCH next from Account_Info into
                    @tempARKey, @acctTotal, @share, @paidToCurrentGL, @priority
                WHILE @@FETCH_STATUS = 0
                BEGIN
                    SET @acctAmount = ROUND(@dueAmount * @share, 2)
                    /*
                        Round-off correction if this is the last payment
                    */
                    if (@currentPayment = @payments) AND (@acctAmount + @paidToCurrentGL != @acctTotal)
                        SET @acctAmount = @acctTotal - @paidToCurrentGL

                    SET @paidToCurrentGL = @paidToCurrentGL + @acctAmount
                    UPDATE @Totals SET Paid = @paidToCurrentGL WHERE GLAccountKey = @tempARKey                    
    
                    SET @scheduledSoFarForInstallment = @scheduledSoFarForInstallment + @acctAmount
                
                    SET @scheduleLineKey = NEWID()
                    INSERT PaymentScheduleLine(PaymentScheduleLineKey, InvoiceKey, PaymentNumber, DueDate, AmountDue, OutstandingBalance, GLAccountKey, PayPriority)
                    VALUES(@scheduleLineKey, @prevInvKey, @currentPayment, @paymentDate, @acctAmount, @acctAmount, @tempARKey, @priority)
                    FETCH next from Account_Info into
                        @tempARKey, @acctTotal, @share, @paidToCurrentGL, @priority
                END
                CLOSE Account_Info
                DEALLOCATE Account_Info    
                /* If the total amount we added to the various accounts
                   does not equal the amount due for this payment, we will
                   adjust the last line to account for the rounding error. */
                IF  @scheduledSoFarForInstallment != @dueAmount
                BEGIN
                    SET @acctAmount = @acctAmount + (@dueAmount - @scheduledSoFarForInstallment)
                    UPDATE PaymentScheduleLine SET AmountDue = @acctAmount WHERE PaymentScheduleLineKey = @scheduleLineKey
                END
                
            END
            ELSE -- Cash-based; set PayPriority to zero for now -- Revisit this
            BEGIN
                SET @scheduleLineKey = NEWID()
                INSERT PaymentScheduleLine (PaymentScheduleLineKey, InvoiceKey, PaymentNumber, DueDate, AmountDue, OutstandingBalance, PayPriority)
                VALUES (@scheduleLineKey, @prevInvKey, @currentPayment, @paymentDate, @dueAmount, @dueAmount, 0)
            END
                
        END
        /* We are finished processing the previous invoice.  If there are no more
           lines (fetch status was -1) we are done.  Otherwise we
           re-initialize for the current, new, invoice.
        */
        IF @fetchStatus = -1 BREAK
        IF @fetchStatus = -2 CONTINUE
        SET @invoiceTotal = 0;
        DELETE @Totals
    END
    IF (@prevInvKey != @invKey)
    BEGIN
        -- Starting a new invoice; set all the "prev" variables.
        SET @prevInvKey = @invKey
        SET @prevPaymentTermsKey = @paymentTermsKey
        SET @prevInvDate =  @invDate
        SET @prevIntSize = @intSize
        SET @prevIntType = @intType
        SET @prevIntCount = @intCount
        SET @prevAcctMethod = @acctMethod
        SET @prevFirstPaymentDateTypeCode =  @firstPaymentDateTypeCode
        SET @prevFirstPaymentDateOffset =  @firstPaymentDateOffset
        SET @prevFirstPaymentDueDate = @firstPaymentDueDate
    END
    SET @firstIteration = 0
    IF @prevInvLineKey is null OR @invLineKey != @prevInvLineKey
    BEGIN
        SET @invoiceTotal = @invoiceTotal + @extendedPrice
        /* Associate the invoice distribution amounts with different GLAccounts
           depending on the accounting method.
        */
        IF UPPER(@acctMethod) = 'A'
        BEGIN
            /*
                Update the table variable Totals with information from this
                invoice distribution.  But first, insert into Totals if we have not encountered
                this GLAccount in this invoice yet.
                 
                Note that we run this code only once per invoice line.  Multiple distributions for
                this invoice line will have the same ARAcctKey, and will share the same ExtendedPrice.
                If we run this for each distribution, we will count the ExtendedPrice multiple times.
            */
            SET @currentTotal = null
            SELECT @currentTotal = Total, @priority = Priority from @Totals where GLAccountKey = @ARAcctKey
            IF @currentTotal IS NULL
            BEGIN
                INSERT @Totals(GLAccountKey, Total, Share, Paid, Priority)
                VALUES (@ARAcctKey, 0, 0, 0, @payPriority)
                SET @currentTotal = 0
                SET @priority = @payPriority
            END
            SET @currentTotal = @currentTotal + @extendedPrice
            if @payPriority < @priority
                SET @priority = @payPriority
            UPDATE @Totals SET Total = @currentTotal, Priority = @priority WHERE GLAccountKey = @ARAcctKey
        END
    END
    SET @prevInvLineKey = @invLineKey
    FETCH next from Get_Invoices into
        @invKey, @invLineKey, @acctMethod, @paymentTermsKey, @invDate, @extendedPrice, @termsCode, @earlyDiscountDays, @earlyDiscountPercent, @intSize, @intType, @intCount,
        @ARAcctKey, @firstPaymentDueDate, @firstPaymentDateTypeCode, @firstPaymentDateOffset, @payPriority
END
CLOSE Get_Invoices
DEALLOCATE Get_Invoices

GO